Imports DTO
Imports System.Data.OleDb

Public Class LopHocDAO
    Inherits AbstractDAO
    Public Sub New()

    End Sub
    Public Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From LopHoc"
        'B4: Thuc thi chuoi strSQL
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function
    Public Sub CapNhatBang(ByVal dt As DataTable)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From LopHoc"
        'B4: Thuc thi chuoi strSQL
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub
    Public Function LayDanhSach() As IList
        Dim ds As New ArrayList()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From LopHoc"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Dim lhDto As New LopHocDto
            lhDto.MaLopHoc = dr("MaLopHoc")
            lhDto.TenLopHoc = dr("TenLopHoc")
            lhDto.MaKhoi = dr("MaKhoi")
            ds.Add(lhDto)

        End While
        'B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return ds
    End Function
    Public Function TimKiem(ByVal maLopHoc As String)
        Dim lhDto As New LopHocDto
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From LopHoc Where MaLopHoc = " & maLopHoc
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            lhDto.MaLopHoc = dr("MaLopHoc")
            lhDto.TenLopHoc = dr("TenLopHoc")
            lhDto.MaKhoi = dr("MaKhoi")

        End While
        'B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return lhDto
    End Function
    Public Sub Them(ByVal lhDto As LopHocDto)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into LopHoc( TenLopHoc,MaKhoi)) values " & _
                 " ( '" & lhDto.TenLopHoc & "' ,'" & lhDto.MaKhoi & "')"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cmd = New OleDbCommand(strSQL, cn)
        lhDto.MaLopHoc = cmd.ExecuteScalar()

        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub
    Public Sub Xoa(ByVal maLopHoc As String)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Delete From LopHoc " & _
                 " Where MaLopHoc = " & maLopHoc
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub
    Public Sub Sua(ByVal lhDto As LopHocDto)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Update LopHoc Set " & _
                 " TenLopHoc = '" + lhDto.TenLopHoc & "'and MaKhoi = '" & lhDto.MaKhoi & _
                 "' Where MaLopHoc= " & lhDto.MaLopHoc

        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub



End Class
